package dataaccess;

import java.sql.*;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DataAccess {

    private String database;
    private String user;
    private String pass;
    private static Connection con = null;

    public DataAccess() {


        database = "DbTrieuPhu";
        user = "sa";
        pass = "phamvanthao";

        String url = "jdbc:sqlserver://localhost:1433;databaseName=" + database + ";";
        try {

            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(url, user, pass);
            System.out.println("Connect success!!!");
        } catch (Exception e) {
            System.out.println("Connection erro!!!Please connect try again!!!");
        }
    }

    public ResultSet executeProc(String procName, String[] args) {
        try {
            CallableStatement cs;
            if (args == null) {
                cs = con.prepareCall("{call" + procName + "}");
            } else {
                String sArg = "";
                int n = args.length;
                int i;
                for (i = 0; i < n; i++) {
                    sArg += args[i];
                    if (i != n - 1) {
                        sArg += ",";
                    }
                }
                cs = con.prepareCall("{call " + procName + "(" + sArg + ")}");
            }
            return cs.executeQuery();
        } catch (SQLException ex) {
            Logger.getLogger(DataAccess.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

//    public void executeProc(String procName) {
//        executeProc(procName, null);
//    }
    public static ResultSet executeQuery(String spName) throws SQLException //cau lenh tren thuc hien cau select.
    {
        new DataAccess();
        if (con != null) {
            CallableStatement cs = con.prepareCall("{call " + spName + "}");
            return cs.executeQuery();
        } else {
            System.out.println("Connect Error!!!");
            return null;
        }
    }

    public static ResultSet executeQuery(String spName, Vector paramList) throws SQLException {

        if (con != null) {
            String strQ = "{call " + spName + "(";
            int t = 0;
            for (Object obj : paramList) {
                if (t != 0) {
                    strQ += ",";
                }
                if (obj instanceof Integer) {
                    Integer i = (Integer) obj;
                    strQ += i.toString();
                } else if (obj instanceof String) {
                    String s = (String) obj;
                    strQ += "'" + s + "'";
                }
                t++;
            }
            strQ += ")}";

            CallableStatement cst = con.prepareCall(strQ);
            return cst.executeQuery();
        }
        return null;
    }
        public static int executeUpdate(String spName, Vector paramList) throws SQLException {
        if (con != null) {
            String strQ = "{call " + spName + "(";

            int t = 0;
            for (Object obj : paramList) {
                if (t != 0) {
                    strQ += ",";
                }
                if (obj instanceof Integer) {
                    Integer i = (Integer) obj;
                    strQ += i.toString();
                } else if (obj instanceof Float) {
                    Float f = (Float) obj;
                    strQ += f.toString();
                } else if (obj instanceof String) {
                    String s = (String) obj;
                    strQ += "'" + s + "'";
                }
                t++;
            }
            strQ += ")}";

            CallableStatement cst = con.prepareCall(strQ);
            return cst.executeUpdate();
        }
        return -1;
    }
}
